//分页功能

const express = require('express');
const db = require("../../../util/dbConfig/dbConfig")

module.exports = async function pagingLoad(req, res, where) {
    let {page} = req.body
    //没有页码值，返回错误
    if (!page) {
        return res.send({
            code: 1,
            msg: '参数错误，请输入页码'
        })
    }
    //有页码
    let newPage = (page - 1) * 10 //跳过多页
    try {
        //查询该页的10条数据
        let sySql //单页数据sql语句
        let countSql  //总结果条数
        let {town, village, community, name, groundCount} = where

        //按地址查询模块
        if (where === {}) {
            //无镇村社队，程序异常，阻止执行
            return false
        } else if (town && !village && !community) {
            //有镇，无村社队
            sySql = `SELECT
 a.id id,
 a.NAME userName,
 a.phonenum phone,
 c.NAME town,
 d.NAME village,
 e.NAME community,
 f.NAME term,
 sum( CASE b.sex WHEN 1 THEN 1 ELSE 0 END ) GsheepNum,
 sum( CASE b.sex WHEN 2 THEN 1 ELSE 0 END ) MsheepNum 
FROM
 USER a
 LEFT JOIN sheep b ON b.user_id = a.id
 LEFT JOIN town c ON c.id = a.town_id
 LEFT JOIN village d ON d.id = a.village_id
 LEFT JOIN community e ON e.id = a.community_id
 LEFT JOIN term f ON f.id = a.term_id 
WHERE
 a.ISDELETE=0 AND c.NAME='${town}'
GROUP BY
 a.id,
 a.NAME,
 a.phonenum,
 c.NAME,
 d.NAME,
 e.NAME,
 f.NAME
 LIMIT ${newPage},10`
        } else if (town && village && !community ) {
            //有镇村，无社队
            sySql = `SELECT
 a.id id,
 a.NAME userName,
 a.phonenum phone,
 c.NAME town,
 d.NAME village,
 e.NAME community,
 f.NAME term,
 sum( CASE b.sex WHEN 1 THEN 1 ELSE 0 END ) GsheepNum,
 sum( CASE b.sex WHEN 2 THEN 1 ELSE 0 END ) MsheepNum 
FROM
 USER a
 LEFT JOIN sheep b ON b.user_id = a.id
 LEFT JOIN town c ON c.id = a.town_id
 LEFT JOIN village d ON d.id = a.village_id
 LEFT JOIN community e ON e.id = a.community_id
 LEFT JOIN term f ON f.id = a.term_id 
WHERE
 a.ISDELETE=0 AND c.NAME='${town}' AND d.NAME='${village}'
GROUP BY
 a.id,
 a.NAME,
 a.phonenum,
 c.NAME,
 d.NAME,
 e.NAME,
 f.NAME
 LIMIT ${newPage},10`
        } else if (town && village && community ) {
            //有镇村社，无队
            sySql = `SELECT
 a.id id,
 a.NAME userName,
 a.phonenum phone,
 c.NAME town,
 d.NAME village,
 e.NAME community,
 f.NAME term,
 sum( CASE b.sex WHEN 1 THEN 1 ELSE 0 END ) GsheepNum,
 sum( CASE b.sex WHEN 2 THEN 1 ELSE 0 END ) MsheepNum 
FROM
 USER a
 LEFT JOIN sheep b ON b.user_id = a.id
 LEFT JOIN town c ON c.id = a.town_id
 LEFT JOIN village d ON d.id = a.village_id
 LEFT JOIN community e ON e.id = a.community_id
 LEFT JOIN term f ON f.id = a.term_id 
WHERE
 a.ISDELETE=0 AND c.NAME='${town}' AND d.NAME='${village}' AND e.NAME='${community}'
GROUP BY
 a.id,
 a.NAME,
 a.phonenum,
 c.NAME,
 d.NAME,
 e.NAME,
 f.NAME
 LIMIT ${newPage},10`
        }

        //按用户查找模块
        if (name) {
            //有id
            sySql = `SELECT
 a.id id,
 a.NAME userName,
 a.phonenum phone,
 c.NAME town,
 d.NAME village,
 e.NAME community,
 f.NAME term,
 sum( CASE b.sex WHEN 1 THEN 1 ELSE 0 END ) GsheepNum,
 sum( CASE b.sex WHEN 2 THEN 1 ELSE 0 END ) MsheepNum 
FROM
 USER a
 LEFT JOIN sheep b ON b.user_id = a.id
 LEFT JOIN town c ON c.id = a.town_id
 LEFT JOIN village d ON d.id = a.village_id
 LEFT JOIN community e ON e.id = a.community_id
 LEFT JOIN term f ON f.id = a.term_id 
WHERE
 a.ISDELETE=0 AND a.NAME='${name}'
GROUP BY
 a.id,
 a.NAME,
 a.phonenum,
 c.NAME,
 d.NAME,
 e.NAME,
 f.NAME
 LIMIT ${newPage},10`
        }
        //按园区查询
        if(groundCount){
            sySql = `SELECT
 a.id id,
 a.NAME userName,
 a.phonenum phone,
 c.NAME town,
 d.NAME village,
 e.NAME community,
 f.NAME term,
 sum( CASE b.sex WHEN 1 THEN 1 ELSE 0 END ) GsheepNum,
 sum( CASE b.sex WHEN 2 THEN 1 ELSE 0 END ) MsheepNum 
FROM
 USER a
 LEFT JOIN sheep b ON b.user_id = a.id
 LEFT JOIN town c ON c.id = a.town_id
 LEFT JOIN village d ON d.id = a.village_id
 LEFT JOIN community e ON e.id = a.community_id
 LEFT JOIN term f ON f.id = a.term_id 
WHERE
 a.ISDELETE=0 AND f.NAME='${groundCount}'
GROUP BY
 a.id,
 a.NAME,
 a.phonenum,
 c.NAME,
 d.NAME,
 e.NAME,
 f.NAME
 LIMIT ${newPage},10`
        }
        let sqlArr = []
        let result = await db.SySqlConnect(sySql, sqlArr)
        if (result.length === 0) {
            //如果没有数据，则返回无数据
            return res.send({
                code: 2,
                msg: '该页无数据'
            })
        }
        //查找总条数
        countSql = `SELECT count(*) FROM  (${sySql}) `
        let index = countSql.lastIndexOf('LIMIT') //查找最后一位LIMIT的索引下标
        countSql = countSql.substring(0,index)    //substring(0,最后一位的索引下标值)，截取LIMIT之前的sql语句
        countSql = countSql +')' +'g'    //为sql语句补充substring()删掉的 ')'  和   补充’g‘
        let countAll = await db.SySqlConnect(countSql,sqlArr)
        countAll = countAll[0]['count(*)']
        res.send({
            code: 0,
            msg: '加载成功',
            data: {
                countAll,           //该结果共有多少条数据
                length: result.length,  //当页有多少条数据
                result,                 //当页数据结果
            }
        })
    } catch (e) {
        console.log(e);
        return res.send({
            code: 4,
            msg: '服务器错误，请稍后再试'
        })
    }
}


